﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>稀疏列</title>
    
    <link rel="stylesheet" type="text/css" href="../local/Classic.css">
      
    </link>
    
    <script src="../local/script.js">
      
    </script><script src="../local/script_main.js">&amp;nbsp;</script>
  </head>
  <body>
    <!--Topic built:04/01/2010 05:03:28-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="headerBold">稀疏列</span>
          </td>
          <td align="right">
            
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      
        
        
    <font color="DarkGray">
      
    </font>
    <p />
    
    <p />
  
        <div id="introductionSection" class="section">
    <p>稀疏列是对 Null 值采用优化的存储方式的普通列。稀疏列减少了 Null 值的空间需求，但代价是检索非 Null 值的开销增加。当至少能够节省 20% 到 40% 的空间时，才应考虑使用稀疏列。</p>
    <p>当您连接到 SQL Server 2008 或更高版本的服务器时，SQL Server JDBC Driver 3.0 支持稀疏列。可以使用 <a href="f173fa5d-e114-4a37-a5c4-2baad9ff3af1.htm">SQLServerDatabaseMetaData.getColumns</a>、<a href="e2b0e0f7-717c-48e6-bcd2-a325d938a833.htm">SQLServerDatabaseMetaData.getFunctionColumns</a> 或 <a href="4f0df8fe-3cd6-46e4-ae3c-dc23c35676b2.htm">SQLServerDatabaseMetaData.getProcedureColumns</a> 确定哪个列是稀疏列以及哪个列是列集列。</p>
    <p>列集是返回非类型化 XML 形式的所有稀疏列的计算列。当表中有很多列、大于 1024 或分别对这些稀疏列进行操作很烦琐时，应考虑使用列集。列集最多可以包含 30,000 个列。</p>
  </div><h1 class="heading">示例</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
    </content><sections xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <h1 class="heading" xmlns="">说明</h1><div id="sectionSection" class="section" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <p xmlns="">此示例说明如何检测列集。它还显示如何分析列集的 XML 输出，以便从稀疏列获取数据。</p>
          <p xmlns="">所列的第一个代码部分是您应该对服务器运行的 Transact-SQL。</p>
          <p xmlns="">所列的第二个代码部分是 Java 源代码。在编译应用程序之前，应更改连接字符串中服务器的名称。</p>
        </content></div>
      <h1 class="heading" xmlns="">代码</h1><div id="sectionSection" class="section" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <div class="sampleCode" xmlns=""><span codeLanguage="other"><pre>use AdventureWorks
CREATE TABLE ColdCalling
(
ID int IDENTITY(1,1) PRIMARY KEY,
[Date] date,
[Time] time,
PositiveFirstName nvarchar(50) SPARSE,
PositiveLastName nvarchar(50) SPARSE,
SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);
GO

INSERT ColdCalling ([Date], [Time])
VALUES ('10-13-09','07:05:24')
GO
      
INSERT ColdCalling ([Date], [Time], PositiveFirstName, PositiveLastName)
VALUES ('07-20-09','05:00:24', 'AA', 'B')
GO
      
INSERT ColdCalling ([Date], [Time], PositiveFirstName, PositiveLastName)
VALUES ('07-20-09','05:15:00', 'CC', 'DD')
GO</pre></span></div>
        </content></div>
      <h1 class="heading" xmlns="">代码</h1><div id="sectionSection" class="section" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <div class="sampleCode" xmlns=""><span codeLanguage="other"><pre>import java.sql.*;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.xml.sax.InputSource;

import java.io.StringReader;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class SparseColumns {

   public static void main(String args[]) {
      final String connectionUrl = "jdbc:sqlserver://my_server;databaseName=AdventureWorks;integratedSecurity=true;";
      
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      
      try {
         conn = DriverManager.getConnection(connectionUrl);
         
         stmt = conn.createStatement();
         // Determine the column set column
         String columnSetColName = null;
         String strCmd = "SELECT name FROM sys.columns WHERE object_id=(SELECT OBJECT_ID('ColdCalling')) AND is_column_set = 1";
         rs = stmt.executeQuery(strCmd);
         
         if (rs.next()) {
            columnSetColName = rs.getString(1);
            System.out.println(columnSetColName + " is the column set column!");
         }
         rs.close();

         rs = null; 
             
         strCmd = "SELECT * FROM ColdCalling";
         rs = stmt.executeQuery(strCmd);
            
         // Iterate through the result set
         ResultSetMetaData rsmd = rs.getMetaData();
         
         DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
         DocumentBuilder db = dbf.newDocumentBuilder();
         InputSource is = new InputSource();
         while (rs.next()) {
            // Iterate through the columns
            for (int i = 1; i &lt;= rsmd.getColumnCount(); ++i) {
               String name = rsmd.getColumnName(i);
               String value = rs.getString(i);
   
               // If this is the column set column
               if (name.equalsIgnoreCase(columnSetColName)) {
                  System.out.println(name);
                  
                  // Instead of printing the raw XML, parse it
                  if (value != null) {
                     // Add artificial root node "sparse" to ensure XML is well formed
                     String xml = "&lt;sparse&gt;" + value + "&lt;/sparse&gt;";
   
                     is.setCharacterStream(new StringReader(xml));
                     Document doc = db.parse(is);
   
                     // Extract the NodeList from the artificial root node that was added
                     NodeList list = doc.getChildNodes();
                     // This is the &lt;sparse&gt; node
                     Node root = list.item(0); 
                     // These are the xml column nodes
                     NodeList sparseColumnList = root.getChildNodes(); 
   
                     // Iterate through the XML document
                     for (int n = 0; n &lt; sparseColumnList.getLength(); ++n) {
                        Node sparseColumnNode = sparseColumnList.item(n);
                        String columnName = sparseColumnNode.getNodeName();
                        // Note that the column value is not in the sparseColumNode, it is the value of the first child of it
                        Node sparseColumnValueNode = sparseColumnNode.getFirstChild();
                        String columnValue = sparseColumnValueNode.getNodeValue();
                        
                        System.out.println("\t" + columnName + "\t: " + columnValue);
                     }
                  }
               } else {   // Just print the name + value of non-sparse columns
                  System.out.println(name + "\t: " + value);
               }
            }
            System.out.println();//New line between rows
         }
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if (rs != null) {
            try {
               rs.close();
            } catch (Exception e) {
               e.printStackTrace();
            }
         }
         if (stmt != null) {
            try {
               stmt.close();
            } catch (Exception e) {
               e.printStackTrace();
            }
         }
         if (conn != null) {
            try {
               conn.close();
            } catch (Exception e) {
               e.printStackTrace();
            }
         }
      }
   }      
}</pre></span></div>
        </content></div>
    </sections></div><span id="seeAlsoSpan"><h1 class="heading">请参阅</h1></span><div id="seeAlsoSection" class="section" name="collapseableSection"><a href="e1592499-b87b-45ee-bab8-beaba8fde841.htm">借助 JDBC 驱动程序提高性能和可靠性</a><br /><br /></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]-->
      <div id="footer" class="section">
        
		<hr />
		
		<span id="fb" class="feedbackcss">
			
			
		</span>
		
		<a href="9bad553b-9e70-4696-8499-2e35f772a1e0.htm">
			
			© 2010 Microsoft Corporation。保留所有权利。
		</a>
 	
	
      </div>
    </div>
  </body>
</html>